package com.hsnn.medstgmini.pro.base.dao.Impl;


import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.hsnn.medstgmini.base.dict.dao.impl.BaseHibernateDAO;
import com.hsnn.medstgmini.base.std.model.HospInfo;
import com.hsnn.medstgmini.dao.HibernateSqlUtil;
import com.hsnn.medstgmini.pro.base.dao.DrugShortageDao;
import com.hsnn.medstgmini.pro.base.model.BaseGoods;
import com.hsnn.medstgmini.pro.base.model.form.BaseShortmedicineForm;
import com.hsnn.medstgmini.pro.base.model.form.ShortMedicine;
import com.hsnn.medstgmini.pro.base.service.DrugShortageManager;
import com.hsnn.medstgmini.sys.model.SysUser;
import com.hsnn.medstgmini.util.AreaUtil;
import com.hsnn.medstgmini.util.Pagination;

@Repository
public class DrugShortageDaoImpl extends BaseHibernateDAO implements DrugShortageDao{
	
	@Autowired
	private HibernateSqlUtil dao;

	@Autowired
	private DrugShortageManager drugShortageManager;
	
	@Autowired
	private HibernateSqlUtil sqlUtil;
	
	@Resource(name = "sessionFactory")
	private SessionFactory sessionFactory;
	/**
	 * @category 获取短缺药品信息
	 * @param 
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Map<String, Object> getDrugInfo(String shortmedicine_id){
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT shortmedicine_id ,drug_name ,form_name ,spec_name ,");
        sb.append("factor ,unit_name ,comp_name ,drug_attr ,short_date ,short_type ,need_count ,drug_medicine ,");
        sb.append("shortage_reason ,submit_datetime ,submit_status ,remark ,bid_flag ,");
        sb.append("cancel_remark ,hosp_code  ,hosp_name ,hosp_spel_code ,");
        sb.append("hosp_status,modify_status ,upd_datetime ,upd_user ");
        sb.append("FROM base_shortmedicine WHERE submit_status=1 and shortmedicine_id =:shortmedicine_id");
        Map<String, Object> params = new HashMap<String, Object>();
		params.put("shortmedicine_id", shortmedicine_id);
		return (Map<String, Object>) sqlUtil.getSqlMapUniqueResult(sb.toString(), params);
	}
	/**
	 * @category 查询已申报短缺药品
	 * @param page 分页
	 * @return
	 */
	public Pagination getShortageInfo(Pagination page , HospInfo hospInfo){
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT a.shortmedicine_id ,a.drug_name ,a.form_name ,a.spec_name ,");
		sb.append("a.factor ,a.unit_name ,a.comp_name ,a.drug_attr ,a.short_date,a.need_count ,a.short_type,a.drug_medicine ,");
		sb.append("a.shortage_reason ,a.submit_datetime ,a.submit_status ,a.remark ,a.bid_flag ,");
		sb.append("a.cancel_remark ,a.hosp_code  ,a.hosp_name ,a.hosp_spel_code ,");
		sb.append("a.hosp_status,a.modify_status ,a.upd_datetime ,a.upd_user,");
		sb.append("c.hea_bur_name,c.area_id ");
		sb.append("FROM base_shortmedicine a JOIN std_hospinfo b ON a.hosp_code=b.hosp_code "); 
		sb.append("JOIN std_healthbureau c ON b.area_id=c.area_id "); 
		sb.append("WHERE a.submit_status=1 ");
        sb.append("AND ").append(AreaUtil.getDrugByArea(hospInfo.getArea().getAreaid(), "b.area_id "));
        sb.append("AND a.hosp_code = :hospCode ");
        Map<String,Object> params = new HashMap<String,Object>();
        ShortMedicine sm = (ShortMedicine) page.getForm();
        if (null != sm) {
        	params.put("hospCode", sm.getHosp_code());
        	if(StringUtils.isNotBlank(page.getConditions().get("drugName"))){
        		sb.append(" and drug_name like :drugName");
        		params.put("drugName", "%"+page.getConditions().get("drugName")+"%");
        	}
        	if(StringUtils.isNotBlank(page.getConditions().get("compName"))){
        		sb.append(" and comp_name like :compName");
        		params.put("compName", "%"+page.getConditions().get("compName")+"%");
        	}
        }
		return dao.getSqlPageMapList(sb.toString(), params, page);
	}
	
	/**
	 * @category 根据"短缺申报id"更新已申报短缺药品
	 * @param sm 实体类对象
	 */
	public void UpdateBySMI(ShortMedicine sm ){
		StringBuffer sb = new StringBuffer();
		sb.append("update base_shortmedicine set drug_name=:drug_name, ");
		sb.append("remark=:remark, ");
		sb.append("form_name=:form_name, ");
		sb.append("spec_name=:spec_name, ");
		sb.append("factor=:factor, ");
		sb.append("unit_name=:unit_name, ");
		sb.append("comp_name=:comp_name, ");
		sb.append("drug_attr=:drug_attr, ");
		sb.append("drug_medicine=:drug_medicine, ");
		sb.append("need_count=:need_count, ");
		sb.append("short_date=:short_date, ");
		sb.append("shortage_reason=:shortage_reason,  ");
		sb.append("upd_datetime=sysdate(), ");
		sb.append("upd_user=:upd_user, ");		
		sb.append("short_type=:short_type  ");		
		sb.append("where shortmedicine_id=:shortmedicine_id");
		Map<String, Object> params = new HashMap<String, Object>();		
		params.put("drug_name", sm.getDrug_name());
		params.put("remark", sm.getRemark());
		params.put("form_name", sm.getForm_name());
		params.put("spec_name", sm.getSpec_name());
		params.put("factor", sm.getFactor());
		params.put("unit_name", sm.getUnit_name());
		params.put("comp_name", sm.getComp_name());
		params.put("drug_attr", sm.getDrug_attr());
		params.put("drug_medicine", sm.getDrug_medicine());
		params.put("need_count", sm.getNeed_count());
		params.put("short_date", sm.getShort_date());		
		params.put("shortage_reason", sm.getShortage_reason());		
		params.put("upd_user", sm.getUpd_user());		
		params.put("short_type", sm.getShort_type());		
		params.put("shortmedicine_id", sm.getShortmedicine_id());
		dao.executeSql(sb.toString(), params);
	}
	
	/**
	 * @category 撤销已申报短缺药品改变submit_status状态
	 * @param sm 实体类对象
	 */
	public void UpdateBySS(ShortMedicine sm){
		StringBuffer sb = new StringBuffer();
		sb.append(" update base_shortmedicine set ");
		sb.append(" submit_status=2, ");
		sb.append(" cancel_remark=:cancel_remark ");
		sb.append(" where shortmedicine_id=:shortmedicine_id");
		Map<String, Object> params = new HashMap<String, Object>();		
		params.put("cancel_remark" , sm.getCancel_remark());
		params.put("shortmedicine_id", sm.getShortmedicine_id());
		dao.executeSql(sb.toString(), params);
	}


	
	
	
	
	
	/**
	 * 获取中标药品
	 * @param page
	 * @param hospInfo
	 * @return
	 */
	@Override
	public Pagination getDataList(Pagination page, HospInfo hospInfo) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT a.`status`, a.goods_code goodsCode, a.area_id areaId, a.area_name areaName, "); 
		sql.append(" a.ar_shortname arShortname, a.ar_fullname arFullname, a.proj_id projId, ");
		sql.append(" a.proj_name projName, a.proj_starttime projStarttime, a.proj_endtime projEndtime, ");
		sql.append(" a.proj_status projStatus, a.drug_code drugCode, a.cat_comp_code catCompCode, ");
		sql.append(" a.cat_comp_name catCompName, a.use_range useRange, a.drug_attr drugAttr, ");
		sql.append(" a.goods_source goodsSource, a.pattern_type patternType,  ");
		sql.append(" a.drug_cat drugCat, ");
		sql.append(" a.qualityspec_name qualityspecName, a.form_name formName, a.spec_name specName, ");
		sql.append(" a.material_name materialName, a.unit_name unitName, a.factor, a.prod_compcode compCode, ");
		sql.append(" a.prod_compname compName,a.drug_medicine drugMedicine,c.dic_name goodsSourceName FROM base_goods_item a ");
		sql.append(" JOIN bid_projhospital b ON a.proj_id = b.proj_id ");
		sql.append(" left join dic_sysdic c on a.goods_source = c.dic_code and c.classify_code = 114 ");
		sql.append(" WHERE a.proj_status = 1 ");
		sql.append(" AND a.proj_starttime <= SYSDATE() AND a.proj_endtime >= SYSDATE() ");
		sql.append(" AND a.prod_compstatus = 1 AND a.qualityspec_status = 1 AND a.drug_status = 1 ");
		sql.append(" AND b.`status` = 1 AND b.proj_status = 1 AND b.hosp_code = :hospCode ");
		sql.append(" AND b.proj_starttime <= SYSDATE() AND b.proj_endtime >= SYSDATE() ");
		sql.append(" AND ").append(AreaUtil.getDrugByArea(hospInfo.getArea().getAreaid(), "a.area_id"));
		sql.append(" AND (a.use_range = :useRange or a.use_range=0)");

		BaseGoods form = (BaseGoods) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		
		if (null != form) {
			params.put("useRange", hospInfo.getHospClassify());
			params.put("hospCode", form.getHospCode());
			if (StringUtils.isNotBlank(form.getDrugName())) {
				sql.append(" and (a.qualityspec_name LIKE :drugName or a.qualityspec_name_spel LIKE :drugNameSpel) ");
				params.put("drugName", "%" + form.getDrugName().trim() + "%");
				params.put("drugNameSpel", "%" + form.getDrugName().trim().toUpperCase() + "%");
			}
			if (StringUtils.isNotBlank(form.getCompName())) {
				sql.append(" and (a.prod_compname LIKE :compName or a.prod_compname_spel LIKE :compNameSpel) ");
				params.put("compName", "%" + form.getCompName().trim() + "%");
				params.put("compNameSpel", "%" + form.getCompName().trim().toUpperCase() + "%");
			}
			if (StringUtils.isNotBlank(form.getCatCompName())) {
				sql.append(" and (a.cat_comp_name LIKE :catCompName or a.cat_comp_name_spel LIKE :catCompNameSpel) ");
				params.put("catCompName", "%" + form.getCatCompName().trim() + "%");
				params.put("catCompNameSpel", "%" + form.getCatCompName().trim().toUpperCase() + "%");
			}
		}
		page = dao.getPageMapList(sql.toString(), params, page);
		return page;
	}

	
	/**
	 * 根据id查询企业产品详情
	 */
	@SuppressWarnings("unchecked")
	@Override
	public Map<String, Object> getDrugShortageDetail(String goodsCode,String projId) {
		String sql = " select a.qualityspec_name drugName, a.form_name formName, a.spec_name specName, a.material_name materialName, "
				   + " a.unit_name unitName, a.factor factor,a.drug_medicine drugMedicine,a.cat_comp_name catCompName,a.drug_attr drugAttr "
				   + " from base_goods_item a "
				   + " where goods_code=:goodsCode and proj_id=:projId ";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("goodsCode", goodsCode);
		params.put("projId", projId);
		return (Map<String, Object>) sqlUtil.getSqlMapUniqueResult(sql, params);
	}
	
	/**
	 * 上报短缺药品
	 */
	@Override
	public void saveDrugInfoRep(BaseShortmedicineForm form, SysUser user) {
		StringBuffer sql = new StringBuffer()
		.append(" insert into base_shortmedicine (shortmedicine_id,drug_name,form_name,spec_name,factor,unit_name,comp_name,drug_attr,short_date,need_count,drug_medicine,short_type,shortage_reason,submit_datetime,submit_status,remark,bid_flag,modify_status,upd_datetime,upd_user,hosp_code,hosp_name,hosp_spel_code,hosp_status) ")
		.append(" values(UUID(),:drug_name,:form_name,:spec_name,:factor,:unit_name,:comp_name,:drug_attr,:short_date,:need_count,:drug_medicine,:short_type,:shortage_reason,:submit_datetime,1,:remark,1,0,:upd_datetime,:upd_user,:hosp_code,:hosp_name,:hosp_spel_code,:status) ");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("drug_name", form.getDrugName());
		params.put("form_name", form.getFormName());
		params.put("spec_name", form.getSpecName());
		params.put("factor", form.getFactor());
		params.put("unit_name", form.getUnitName());
		params.put("comp_name", form.getCompName());
		params.put("drug_attr", form.getDrugAttr());
		params.put("short_date", form.getShortDate());
		params.put("need_count", form.getNeedCount());
		params.put("drug_medicine", form.getDrugMedicine());
		params.put("short_type", form.getShortType());
		params.put("shortage_reason", form.getShortageReason());
		params.put("submit_datetime", new Date());
		params.put("remark", form.getRemark());
		params.put("upd_datetime",new Date());
		params.put("upd_user", form.getUpdUser());
		params.put("hosp_code", ((HospInfo) user.getInfo()).getHospCode());
		params.put("hosp_name", ((HospInfo) user.getInfo()).getHospName());
		params.put("hosp_spel_code", ((HospInfo) user.getInfo()).getHospSpelCode());
		params.put("status", ((HospInfo) user.getInfo()).getStatus());
		dao.executeSql(sql.toString(), params);
		
	}

	
	/**
	 * 新增短缺药品
	 */
	@Override
	public void saveDrugInfo(BaseShortmedicineForm form, SysUser user) {
		StringBuffer sql = new StringBuffer()
		.append(" insert into base_shortmedicine (shortmedicine_id,drug_name,form_name,spec_name,factor,unit_name,comp_name,drug_attr,short_date,need_count,drug_medicine,short_type,shortage_reason,submit_datetime,submit_status,remark,modify_status,bid_flag,upd_datetime,upd_user,hosp_code,hosp_name,hosp_spel_code,hosp_status ) ")
		.append(" values(UUID(),:drug_name,:form_name,:spec_name,:factor,:unit_name,:comp_name,:drug_attr,:short_date,:need_count,:drug_medicine,:short_type,:shortage_reason,:submit_datetime,1,:remark,1,0,:upd_datetime,:upd_user,:hosp_code,:hosp_name,:hosp_spel_code,:status) ");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("drug_name", form.getDrugName());
		params.put("form_name", form.getFormName());
		params.put("spec_name", form.getSpecName());
		params.put("factor", form.getFactor());
		params.put("unit_name", form.getUnitName());
		params.put("comp_name", form.getCompName());
		params.put("drug_attr", form.getDrugAttr());
		params.put("short_date", form.getShortDate());
		params.put("need_count", form.getNeedCount());
		params.put("drug_medicine", form.getDrugMedicine());
		params.put("short_type", form.getShortType());
		params.put("shortage_reason", form.getShortageReason());
		params.put("submit_datetime", new Date());
		params.put("remark", form.getRemark());
		params.put("upd_datetime",new Date());
		params.put("upd_user", form.getUpdUser());
		params.put("upd_user", form.getUpdUser());
		params.put("hosp_code", ((HospInfo) user.getInfo()).getHospCode());
		params.put("hosp_name", ((HospInfo) user.getInfo()).getHospName());
		params.put("hosp_spel_code", ((HospInfo) user.getInfo()).getHospSpelCode());
		params.put("status", ((HospInfo) user.getInfo()).getStatus());
		dao.executeSql(sql.toString(), params);
		
	}


	/**
	 * 校验通用名
	 */
	@SuppressWarnings("unchecked")
	public List<BaseGoods> getHospInfoByDrugName(String drugName,String formName,String specName,String factor,String unitName,String compName,String drugAttr) {
		Session session = sessionFactory.getCurrentSession();
		StringBuffer sql = new StringBuffer();
		sql.append(" select * ");
		sql.append(" from base_goods_item ");
		sql.append(" where qualityspec_name = :drugName ");
		sql.append(" AND form_name = :formName ");
		sql.append(" AND spec_name = :specName ");
		sql.append(" AND factor = :factor ");
		sql.append(" AND unit_name = :unitName ");
		sql.append(" AND prod_compname = :compName ");
		sql.append(" AND drug_attr = :drugAttr ");
		Query query = session.createSQLQuery(sql.toString());
		query.setParameter("drugName", drugName);
		query.setParameter("formName", formName);
		query.setParameter("specName", specName);
		query.setParameter("factor", factor);
		query.setParameter("unitName", unitName);
		query.setParameter("compName", compName);
		query.setParameter("drugAttr", drugAttr);
		return query.list();
	}
	
	/**
	 * 查询短缺类型
	 */
	@SuppressWarnings("unchecked")
	public List<Integer> getShortType(){
		Session session = sessionFactory.getCurrentSession();
		StringBuffer sb = new StringBuffer();
		sb.append("select short_type from base_shortmedicine");
		Query query = session.createSQLQuery(sb.toString()).addEntity(ShortMedicine.class);
		
		return query.list();
	}
	
	/**
	 * 卫生局查看下属医院短缺药品
	 */
	@Override
	public Pagination getShortageInfoWSJ(Pagination page) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ");
		sql.append(" a.drug_name drugName, ");
		sql.append(" a.form_name formName, ");
		sql.append(" a.spec_name specName, ");
		sql.append(" a.factor factor, ");
		sql.append(" a.unit_name unitName, ");
		sql.append(" a.comp_name compName, ");
		sql.append(" a.drug_attr drugAttr, ");
		sql.append(" a.short_date shortDate, ");
		sql.append(" a.need_count needCount, ");
		sql.append(" a.drug_medicine drugMedicine, ");
		sql.append(" a.short_type shortType, ");
		sql.append(" a.shortage_reason shortageReason, ");
		sql.append(" DATE_FORMAT(a.submit_datetime,'%Y-%m-%d') submitDatetime, ");
		sql.append(" a.submit_status submitStatus, ");
		sql.append(" a.remark remark, ");
		sql.append(" a.cancel_remark cancelRemark, ");
		sql.append(" a.hosp_code hospCode, ");
		sql.append(" a.hosp_name hospName ");
		sql.append(" FROM base_shortmedicine a ");
		sql.append(" LEFT JOIN std_hospinfo b ON b.hosp_code = a.hosp_code ");
		sql.append(" LEFT JOIN std_healthbureau c ON c.area_id = b.area_id ");
		sql.append(" WHERE c.hea_bur_id = :orgId ");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("orgId", page.getConditions().get("orgId"));
		BaseShortmedicineForm form = (BaseShortmedicineForm) page.getForm();
		if (StringUtils.isNotBlank(form.getDrugName())) {
			sql.append(" and a.drug_name like :drugName");
			params.put("drugName", "%" +form.getDrugName() +"%");
		}
		if (StringUtils.isNotBlank(form.getCompName())) {
			sql.append(" and a.comp_name like :compName");
			params.put("compName", "%" +form.getCompName() +"%");
		}
		return page = dao.getPageMapList(sql.toString(), params, page);
	}

	
		/**
		 * @category 中心端查询短缺药品
		 * @param page 分页
		 * @return
		 */
	public Pagination getAllShortage(Pagination page) {
			StringBuffer sb = new StringBuffer();
			sb.append("SELECT ");
			sb.append("shortmedicine_id shortmedicineId, ");
			sb.append("drug_name drugName, ");
			sb.append("form_name formName, ");
			sb.append("spec_name specName, ");
			sb.append("factor factor, ");
			sb.append("unit_name unitName, ");
			sb.append("comp_name compName, ");
			sb.append("drug_attr drugAttr, ");
			sb.append("short_date shortDate, ");
			sb.append("need_count needCount, ");
			sb.append("drug_medicine drugMedicine, ");
			sb.append("short_type shortType, ");
			sb.append("shortage_reason shortageReason, ");
			sb.append("date_format(submit_datetime,'%Y-%m-%d %H:%i:%S') submitDatetime, ");
			sb.append("submit_status submitStatus, ");
			sb.append("remark remark, ");
			sb.append("bid_flag bidFlag, ");
			sb.append("cancel_remark cancelRemark, ");
			sb.append("hosp_code hospCode, ");
			sb.append("hosp_name hospName ");			
			sb.append("FROM base_shortmedicine "); 			
			sb.append("WHERE 1=1 ");	      
	        Map<String,Object> params = new HashMap<String,Object>();
	        	if(StringUtils.isNotBlank(page.getConditions().get("drugName"))){
	        		sb.append(" and drug_name like :drugName");
	        		params.put("drugName", "%"+page.getConditions().get("drugName")+"%");
	        	}
	        	if(StringUtils.isNotBlank(page.getConditions().get("compName"))){
	        		sb.append(" and comp_name like :compName");
	        		params.put("compName", "%"+page.getConditions().get("compName")+"%");
	        	}
			return dao.getSqlPageMapList(sb.toString(), params, page);
		
	}
	/**
	 * 一个月只能提交一次
	 */
		@SuppressWarnings("unchecked")
		@Override
		public List<BaseShortmedicineForm> getHospInfoListByDrugNameHosp(String drugName,String formName,String specName,String factor,String unitName,String compName,String drugAttr) {
			Session session = sessionFactory.getCurrentSession();
			StringBuffer sql = new StringBuffer();
			sql.append(" select * ");
			sql.append(" from base_shortmedicine ");
			sql.append(" where drug_name = :drugName ");
			sql.append(" AND form_name = :formName ");
			sql.append(" AND spec_name = :specName ");
			sql.append(" AND factor = :factor ");
			sql.append(" AND unit_name = :unitName ");
			sql.append(" AND comp_name = :compName ");
			sql.append(" AND drug_attr = :drugAttr ");
			sql.append(" AND DATE_FORMAT(submit_datetime,'%Y-%m') = DATE_FORMAT(SYSDATE(),'%Y-%m') ");
			Query query = session.createSQLQuery(sql.toString());
			query.setParameter("drugName", drugName);
			query.setParameter("formName", formName);
			query.setParameter("specName", specName);
			query.setParameter("factor", factor);
			query.setParameter("unitName", unitName);
			query.setParameter("compName", compName);
			query.setParameter("drugAttr", drugAttr);
			return query.list();
		}
	
}

